package report

import (
	"../common"
	"../web"
	"encoding/json"
	"fmt"
	"strconv"
	"strings"
	"time"
)

type QueryKanBanObj struct {
	common.BaseHandler
}

type QueryKanBanPassObj struct {
	common.BaseHandler
}

type KanBanPassPageObj struct {
	common.BaseHandler
}

type QueryKanBanFailObj struct {
	common.BaseHandler
}

type KanBanFailPageObj struct {
	common.BaseHandler
}

func (self *QueryKanBanObj) Prepare() {
	items := *common.FetchAllRowsPtr(
		`SELECT ITEM, ISNULL(DESCRIP, '') 
           FROM SFC_ITEMS(NOLOCK) 
          WHERE LOCKED = 0 
          ORDER BY ITEM`,
		self.Usr.Conn, new(common.ItemDesc))

	self.TmplData["items"] = items
}

func (self *QueryKanBanObj) ParameterCheck() bool {
	begin := strings.TrimSpace(self.Ctx.Params["fromdate"])
	if begin == "" {
		bdate := time.Now().Format("2006-01-02")
		begin = fmt.Sprintf("%s 00:00:00", bdate)
	}
	s_begin, err := time.Parse("2006-01-02 15:04:05", begin)
	if err != nil {
		self.TmplData["error"] = common.GetText(self.Lang, "Query Date is invalid.")
		return false
	}
	if len(begin) != 19 {
		self.TmplData["error"] = common.GetText(self.Lang, "Query Date is invalid.")
		return false
	}

	s_end := time.Now()
	c_end := s_end.Format("2006-01-02 15:04:05")
	if s_begin.Add(3600 * 24 * 60 * time.Second).Before(s_end) {
		self.TmplData["error"] = common.GetText(self.Lang, "Date interval can select up to 60 days.")
		return false
	}

	s_item := strings.TrimSpace(self.Ctx.Params["item"])
	if s_item == "" {
		s_item = "All"
	}

	begin = s_begin.Format("2006-01-02 15:04:05")

	self.Sqlparas = append(self.Sqlparas, s_item)
	self.Sqlparas = append(self.Sqlparas, begin)
	self.Sqlparas = append(self.Sqlparas, c_end)

	return true
}

func (self *QueryKanBanObj) SqlQueryExecute() {
	c_item := self.Sqlparas[0].(string)
	begin := self.Sqlparas[1].(string)
	s_end := self.Sqlparas[2].(string)

	total_map := make(map[string]*common.AnalyseDetails)
	totals := &common.AnalyseDetails{"Total", 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, "100.0", "100.0", "100.0", "100.0", "100.0", "100.0"}

	var proc_num []interface{}

	if c_item == "All" {
		proc_num = *common.FetchAllRowsPtr(
			`SELECT DISTINCT SSN_NO, CURR_STATION
		   	   FROM SFC_SSNFLOW(NOLOCK) 
		      WHERE MODIFYDATE BETWEEN ? AND ?`,
			self.Usr.Conn, new(common.NormalStruct), begin, s_end)
	} else {
		proc_num = *common.FetchAllRowsPtr(
			`SELECT DISTINCT A.SSN_NO, A.CURR_STATION
		   	   FROM SFC_SSNFLOW A(NOLOCK) , SFC_SSN B(NOLOCK), SFC_WO C(NOLOCK)
		      WHERE A.MODIFYDATE BETWEEN ? AND ?
		        AND A.SSN_NO=B.SSN_NO
		        AND B.WONO=C.WONO
		        AND C.ITEM=?`,
			self.Usr.Conn, new(common.NormalStruct), begin, s_end, c_item)
	}
	for _, val := range proc_num {
		stat := strings.TrimSpace(val.(common.NormalStruct).Value2)
		_, ok := total_map[stat]
		if !ok {
			total_map[stat] = &common.AnalyseDetails{stat, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, "100.0", "100.0", "100.0", "100.0", "100.0", "100.0"}
		}
		total_map[stat].Input = total_map[stat].Input + 1
		totals.Input = totals.Input + 1
	}

	if c_item == "All" {
		proc_num = *common.FetchAllRowsPtr(
			`SELECT DISTINCT SSN_NO, CURR_STATION
		   	   FROM SFC_SSNFLOW(NOLOCK) 
		      WHERE MODIFYDATE BETWEEN ? AND ?
		        AND RESULT='PASS'`,
			self.Usr.Conn, new(common.NormalStruct), begin, s_end)
	} else {
		proc_num = *common.FetchAllRowsPtr(
			`SELECT DISTINCT A.SSN_NO, A.CURR_STATION
		   	   FROM SFC_SSNFLOW A(NOLOCK) , SFC_SSN B(NOLOCK), SFC_WO C(NOLOCK)
		      WHERE A.MODIFYDATE BETWEEN ? AND ?
		        AND A.SSN_NO=B.SSN_NO
		        AND B.WONO=C.WONO
		        AND C.ITEM=?
		        AND A.RESULT='PASS'`,
			self.Usr.Conn, new(common.NormalStruct), begin, s_end, c_item)
	}
	for _, val := range proc_num {
		stat := strings.TrimSpace(val.(common.NormalStruct).Value2)
		_, ok := total_map[stat]
		if ok {
			total_map[stat].Output = total_map[stat].Output + 1
			totals.Output = totals.Output + 1
		}
	}

	if c_item == "All" {
		proc_num = *common.FetchAllRowsPtr(
			`SELECT CURR_STATION, RESULT, COUNT(1) AS CNT
			   FROM SFC_SSN(NOLOCK) 
			  WHERE CURR_STATION IN (SELECT STATION FROM SFC_STATION(NOLOCK))
			  GROUP BY CURR_STATION, RESULT`,
			self.Usr.Conn, new(common.ParasStruct3))
	} else {
		proc_num = *common.FetchAllRowsPtr(
			`SELECT A.CURR_STATION, A.RESULT, COUNT(1) AS CNT
			   FROM SFC_SSN A(NOLOCK), SFC_WO B(NOLOCK)
			  WHERE A.CURR_STATION IN 
			  		(
			  			SELECT STATION 
			  			  FROM SFC_STATION(NOLOCK)
			  		)
				AND A.WONO=B.WONO 
				AND B.ITEM=?
			  GROUP BY A.CURR_STATION, A.RESULT`,
			self.Usr.Conn, new(common.ParasStruct3), c_item)
	}
	for _, val := range proc_num {
		stat := strings.TrimSpace(val.(common.ParasStruct3).Value1)
		rsts := strings.TrimSpace(val.(common.ParasStruct3).Value2)
		icnt, _ := strconv.Atoi(val.(common.ParasStruct3).Value3)
		_, ok := total_map[stat]
		if ok {
			if rsts == "PASS" {
				total_map[stat].Inprocess = icnt
				totals.Inprocess = totals.Inprocess + icnt
			} else {
				total_map[stat].FailUnit = icnt
				totals.FailUnit = totals.FailUnit + icnt
			}
		}
	}

	if c_item == "All" {
		proc_num = *common.FetchAllRowsPtr(
			`SELECT RN=ROW_NUMBER() OVER(PARTITION BY SSN_NO, CURR_STATION ORDER BY MODIFYDATE), CURR_STATION, RESULT
			   FROM SFC_SSNFLOW(NOLOCK) 
			  WHERE EXISTS 
			  		(
						SELECT 1 
						  FROM 
						  (
							SELECT DISTINCT SSN_NO, CURR_STATION
						  	  FROM SFC_SSNFLOW(NOLOCK) 
						 	 WHERE MODIFYDATE BETWEEN ? AND ?
						  ) A 
					  	 WHERE A.SSN_NO=SFC_SSNFLOW.SSN_NO AND A.CURR_STATION=SFC_SSNFLOW.CURR_STATION
					)`,
			self.Usr.Conn, new(common.ParasStruct3), begin, s_end)
	} else {
		proc_num = *common.FetchAllRowsPtr(
			`SELECT RN=ROW_NUMBER() OVER(PARTITION BY SSN_NO, CURR_STATION ORDER BY MODIFYDATE), CURR_STATION, RESULT
			   FROM SFC_SSNFLOW(NOLOCK) 
			  WHERE EXISTS 
			  		(
						SELECT 1 
						  FROM 
						  (
							SELECT DISTINCT A.SSN_NO, A.CURR_STATION
					   	      FROM SFC_SSNFLOW A(NOLOCK) , SFC_SSN B(NOLOCK), SFC_WO C(NOLOCK)
					         WHERE A.MODIFYDATE BETWEEN ? AND ?
					           AND A.SSN_NO=B.SSN_NO
					           AND B.WONO=C.WONO
					           AND C.ITEM=?
						  ) A 
					  	 WHERE A.SSN_NO=SFC_SSNFLOW.SSN_NO AND A.CURR_STATION=SFC_SSNFLOW.CURR_STATION
					)`,
			self.Usr.Conn, new(common.ParasStruct3), begin, s_end, c_item)
	}
	for _, val := range proc_num {
		idxd, _ := strconv.Atoi(val.(common.ParasStruct3).Value1)
		stat := strings.TrimSpace(val.(common.ParasStruct3).Value2)
		rsts := strings.TrimSpace(val.(common.ParasStruct3).Value3)
		_, ok := total_map[stat]
		if ok {
			if idxd == 1 {
				if rsts == "PASS" {
					total_map[stat].PUnit = total_map[stat].PUnit + 1
					totals.PUnit = totals.PUnit + 1
				} else {
					total_map[stat].F1Unit = total_map[stat].F1Unit + 1
					totals.F1Unit = totals.F1Unit + 1
				}
			}
			if idxd == 2 {
				if rsts != "PASS" {
					total_map[stat].F2Unit = total_map[stat].F2Unit + 1
					totals.F2Unit = totals.F2Unit + 1
				}
			}
			if idxd > 2 {
				if rsts != "PASS" {
					total_map[stat].F3Unit = total_map[stat].F3Unit + 1
					totals.F3Unit = totals.F3Unit + 1
				}
			}
		}
	}

	if c_item == "All" {
		proc_num = *common.FetchAllRowsPtr(
			`SELECT SSN_NO, FAILSTATION, COUNT(1) AS CNT
			   FROM SFC_REPAIR(NOLOCK) 
			  WHERE EXISTS 
			  		(
						SELECT 1 
						  FROM 
						  (
							SELECT DISTINCT SSN_NO, CURR_STATION
						  	  FROM SFC_SSNFLOW(NOLOCK) 
						 	 WHERE MODIFYDATE BETWEEN ? AND ?
						  ) A 
					  	 WHERE A.SSN_NO=SFC_REPAIR.SSN_NO AND A.CURR_STATION=SFC_REPAIR.FAILSTATION
					)
			  GROUP BY SSN_NO, FAILSTATION`,
			self.Usr.Conn, new(common.ParasStruct3), begin, s_end)
	} else {
		proc_num = *common.FetchAllRowsPtr(
			`SELECT SSN_NO, FAILSTATION, COUNT(1) AS CNT
			   FROM SFC_REPAIR(NOLOCK) 
			  WHERE EXISTS 
			  		(
						SELECT 1 
						  FROM 
						  (
							SELECT DISTINCT A.SSN_NO, A.CURR_STATION
					   	      FROM SFC_SSNFLOW A(NOLOCK) , SFC_SSN B(NOLOCK), SFC_WO C(NOLOCK)
					         WHERE A.MODIFYDATE BETWEEN ? AND ?
					           AND A.SSN_NO=B.SSN_NO
					           AND B.WONO=C.WONO
					           AND C.ITEM=?
						  ) A 
					  	 WHERE A.SSN_NO=SFC_REPAIR.SSN_NO AND A.CURR_STATION=SFC_REPAIR.FAILSTATION
					)
			  GROUP BY SSN_NO, FAILSTATION`,
			self.Usr.Conn, new(common.ParasStruct3), begin, s_end, c_item)
	}
	for _, val := range proc_num {
		stat := strings.TrimSpace(val.(common.ParasStruct3).Value2)
		icnt, _ := strconv.Atoi(val.(common.ParasStruct3).Value3)
		_, ok := total_map[stat]
		if ok {
			if icnt == 1 {
				total_map[stat].P1Unit = total_map[stat].P1Unit + 1
				totals.P1Unit = totals.P1Unit + 1
			}
			if icnt == 2 {
				total_map[stat].P2Unit = total_map[stat].P2Unit + 1
				totals.P2Unit = totals.P2Unit + 1
			}
			if icnt > 2 {
				total_map[stat].P3Unit = total_map[stat].P3Unit + 1
				totals.P3Unit = totals.P3Unit + 1
			}
		}
	}

	totalfpyv := 1.0
	for _, v := range total_map {
		if v.Input == 0 {
			v.FpyYield = "0.0"
			v.PyYield = "0.0"
			v.RpYield1 = "0.0"
			v.RpYield2 = "0.0"
			v.RpYield3 = "0.0"
			continue
		}
		subfpyv := float64(v.PUnit) / float64(v.Input) * 100.0
		subpyv := float64(v.Output) / float64(v.Input) * 100.0
		subrpv1 := float64(v.P1Unit) / float64(v.F1Unit) * 100.0
		subrpv2 := float64(v.P2Unit) / float64(v.F2Unit) * 100.0
		subrpv3 := float64(v.P3Unit) / float64(v.F3Unit) * 100.0
		subrpv := float64(v.P1Unit+v.P2Unit+v.P3Unit) / float64(v.F1Unit+v.F2Unit+v.F3Unit) * 100.0
		if v.F1Unit == 0 {
			subrpv1 = 100.0
		}
		if subrpv1 > 100.0 {
			subrpv1 = 100.0
		}
		if v.F2Unit == 0 {
			subrpv2 = 100.0
		}
		if subrpv2 > 100.0 {
			subrpv2 = 100.0
		}
		if v.F3Unit == 0 {
			subrpv3 = 100.0
		}
		if subrpv3 > 100.0 {
			subrpv3 = 100.0
		}
		if v.F1Unit+v.F2Unit+v.F3Unit == 0 {
			subrpv = 100.0
		}
		if subrpv > 100.0 {
			subrpv = 100.0
		}
		if subfpyv > 0.0 && subfpyv < 100.0 {
			v.FpyYield = strconv.FormatFloat(subfpyv, 'f', 2, 64)
			totalfpyv = totalfpyv * subfpyv / 100.0
		} else {
			v.FpyYield = strconv.FormatFloat(subfpyv, 'f', 1, 64)
		}
		if subpyv > 0.0 && subpyv < 100.0 {
			v.PyYield = strconv.FormatFloat(subpyv, 'f', 2, 64)
		} else {
			v.PyYield = strconv.FormatFloat(subpyv, 'f', 1, 64)
		}
		if subrpv1 > 0.0 && subrpv1 < 100.0 {
			v.RpYield1 = strconv.FormatFloat(subrpv1, 'f', 2, 64)
		} else {
			v.RpYield1 = strconv.FormatFloat(subrpv1, 'f', 1, 64)
		}
		if subrpv2 > 0.0 && subrpv2 < 100.0 {
			v.RpYield2 = strconv.FormatFloat(subrpv2, 'f', 2, 64)
		} else {
			v.RpYield2 = strconv.FormatFloat(subrpv2, 'f', 1, 64)
		}
		if subrpv3 > 0.0 && subrpv3 < 100.0 {
			v.RpYield3 = strconv.FormatFloat(subrpv3, 'f', 2, 64)
		} else {
			v.RpYield3 = strconv.FormatFloat(subrpv3, 'f', 1, 64)
		}
		if subrpv > 0.0 && subrpv < 100.0 {
			v.RpYield = strconv.FormatFloat(subrpv, 'f', 2, 64)
		} else {
			v.RpYield = strconv.FormatFloat(subrpv, 'f', 1, 64)
		}
		v.F1Unit = v.F1Unit - v.P1Unit
		v.F2Unit = v.F2Unit - v.P2Unit
		v.F3Unit = v.F3Unit - v.P3Unit
		if v.F1Unit < 0 {
			v.F1Unit = 0
		}
		if v.F2Unit < 0 {
			v.F2Unit = 0
		}
		if v.F3Unit < 0 {
			v.F3Unit = 0
		}
	}
	if totals.Input == 0 {
		totals.FpyYield = "0.0"
		totals.PyYield = "0.0"
		totals.RpYield1 = "0.0"
		totals.RpYield2 = "0.0"
		totals.RpYield3 = "0.0"
	} else {
		if totalfpyv > 0.0 && totalfpyv < 1.0 {
			totals.FpyYield = strconv.FormatFloat(totalfpyv*100.0, 'f', 2, 64)
		} else {
			totals.FpyYield = strconv.FormatFloat(totalfpyv*100.0, 'f', 1, 64)
		}
		totalpyv := float64(totals.Output) / float64(totals.Input) * 100.0
		if totalpyv > 0.0 && totalpyv < 100.0 {
			totals.PyYield = strconv.FormatFloat(totalpyv, 'f', 2, 64)
		} else {
			totals.PyYield = strconv.FormatFloat(totalpyv, 'f', 1, 64)
		}
		totalrpv1 := float64(totals.P1Unit) / float64(totals.F1Unit) * 100.0
		totalrpv2 := float64(totals.P2Unit) / float64(totals.F2Unit) * 100.0
		totalrpv3 := float64(totals.P3Unit) / float64(totals.F3Unit) * 100.0
		totalrpv := float64(totals.P1Unit+totals.P2Unit+totals.P3Unit) / float64(totals.F1Unit+totals.F2Unit+totals.F3Unit) * 100.0
		if totals.F1Unit == 0 {
			totalrpv1 = 100.0
		}
		if totalrpv1 > 100.0 {
			totalrpv1 = 100.0
		}
		if totals.F2Unit == 0 {
			totalrpv2 = 100.0
		}
		if totalrpv2 > 100.0 {
			totalrpv2 = 100.0
		}
		if totals.F3Unit == 0 {
			totalrpv3 = 100.0
		}
		if totalrpv3 > 100.0 {
			totalrpv3 = 100.0
		}
		if totals.F1Unit+totals.F2Unit+totals.F3Unit == 0 {
			totalrpv = 100.0
		}
		if totalrpv > 100.0 {
			totalrpv = 100.0
		}
		if totalrpv1 > 0.0 && totalrpv1 < 100.0 {
			totals.RpYield1 = strconv.FormatFloat(totalrpv1, 'f', 2, 64)
		} else {
			totals.RpYield1 = strconv.FormatFloat(totalrpv1, 'f', 1, 64)
		}
		if totalrpv2 > 0.0 && totalrpv2 < 100.0 {
			totals.RpYield2 = strconv.FormatFloat(totalrpv2, 'f', 2, 64)
		} else {
			totals.RpYield2 = strconv.FormatFloat(totalrpv2, 'f', 1, 64)
		}
		if totalrpv3 > 0.0 && totalrpv3 < 100.0 {
			totals.RpYield3 = strconv.FormatFloat(totalrpv3, 'f', 2, 64)
		} else {
			totals.RpYield3 = strconv.FormatFloat(totalrpv3, 'f', 1, 64)
		}
		if totalrpv > 0.0 && totalrpv < 100.0 {
			totals.RpYield = strconv.FormatFloat(totalrpv, 'f', 2, 64)
		} else {
			totals.RpYield = strconv.FormatFloat(totalrpv, 'f', 1, 64)
		}
		totals.F1Unit = totals.F1Unit - totals.P1Unit
		totals.F2Unit = totals.F2Unit - totals.P2Unit
		totals.F3Unit = totals.F3Unit - totals.P3Unit
		if totals.F1Unit < 0 {
			totals.F1Unit = 0
		}
		if totals.F2Unit < 0 {
			totals.F2Unit = 0
		}
		if totals.F3Unit < 0 {
			totals.F3Unit = 0
		}
	}

	var total_sta []*common.AnalyseDetails
	for _, v := range total_map {
		total_sta = append(total_sta, v)
	}

	self.TmplData["begindate"] = begin
	self.TmplData["enddate"] = s_end
	self.TmplData["item"] = c_item
	self.TmplData["total_sta"] = total_sta
	self.TmplData["totals"] = totals
}

func QueryKanBanHandler(ctx *web.Context) {
	self := new(QueryKanBanObj)
	self.Ctx = ctx
	self.Html = "KanBan.html"
	self.Rightlist = []string{"kanban"}
	self.DoQuery(self)
}

func (self *QueryKanBanPassObj) ParameterCheck() bool {
	s_nm := self.Ctx.Params["nm"] // query result data number
	s_st := self.Ctx.Params["st"] // station
	s_im := self.Ctx.Params["im"] // item
	s_bd := self.Ctx.Params["bd"] // begin date
	s_fd := self.Ctx.Params["fd"] // end date

	self.Sqlparas = append(self.Sqlparas, s_nm)
	self.Sqlparas = append(self.Sqlparas, s_st)
	self.Sqlparas = append(self.Sqlparas, s_im)
	self.Sqlparas = append(self.Sqlparas, s_bd)
	self.Sqlparas = append(self.Sqlparas, s_fd)

	return true
}

func (self *QueryKanBanPassObj) SqlQueryExecute() {
	s_nm := self.Sqlparas[0].(string)
	s_st := self.Sqlparas[1].(string)
	s_im := self.Sqlparas[2].(string)
	s_bd := self.Sqlparas[3].(string)
	s_fd := self.Sqlparas[4].(string)
	var data_ls []interface{}
	if s_st == "Total" {
		if s_im == "All" {
			data_ls = *common.FetchAllRowsPtr(
				`SELECT TOP 10 ROW_NUMBER() OVER(ORDER BY A.MODIFYDATE) AS AUTOID, B.WONO, C.ITEM, 
						 ISNULL(D.DESCRIP, ''), A.SSN_NO, A.CURR_STATION, A.RESULT, CONVERT(CHAR(23), A.MODIFYDATE, 121)
	           	   FROM SFC_SSNFLOW A(NOLOCK), SFC_SSN B(NOLOCK), SFC_WO C(NOLOCK), SFC_ITEMS D(NOLOCK) 
	          	  WHERE A.MODIFYDATE BETWEEN ? AND ?
	            	AND A.RESULT='PASS'
	            	AND A.SSN_NO=B.SSN_NO
	            	AND B.WONO=C.WONO
	            	AND C.ITEM=D.ITEM`,
				self.Usr.Conn, new(common.QueryDetail), s_bd, s_fd)
		} else {
			data_ls = *common.FetchAllRowsPtr(
				`SELECT TOP 10 ROW_NUMBER() OVER(ORDER BY A.MODIFYDATE) AS AUTOID, B.WONO, C.ITEM,
						 ISNULL(D.DESCRIP, ''), A.SSN_NO, A.CURR_STATION, A.RESULT, CONVERT(CHAR(23), A.MODIFYDATE, 121)
	           	   FROM SFC_SSNFLOW A(NOLOCK), SFC_SSN B(NOLOCK), SFC_WO C(NOLOCK), SFC_ITEMS D(NOLOCK) 
	          	  WHERE A.MODIFYDATE BETWEEN ? AND ?
	            	AND A.RESULT='PASS'
	            	AND A.SSN_NO=B.SSN_NO
	            	AND B.WONO=C.WONO
	            	AND C.ITEM=D.ITEM
	            	AND C.ITEM=?`,
				self.Usr.Conn, new(common.QueryDetail), s_bd, s_fd, s_im)
		}
	} else {
		if s_im == "All" {
			data_ls = *common.FetchAllRowsPtr(
				`SELECT TOP 10 ROW_NUMBER() OVER(ORDER BY A.MODIFYDATE) AS AUTOID, B.WONO, C.ITEM, 
						 ISNULL(D.DESCRIP, ''), A.SSN_NO, A.CURR_STATION, A.RESULT, CONVERT(CHAR(23), A.MODIFYDATE, 121)
	           	   FROM SFC_SSNFLOW A(NOLOCK), SFC_SSN B(NOLOCK), SFC_WO C(NOLOCK), SFC_ITEMS D(NOLOCK) 
	          	  WHERE A.MODIFYDATE BETWEEN ? AND ?
			    	AND A.CURR_STATION=?
	            	AND A.RESULT='PASS'
	            	AND A.SSN_NO=B.SSN_NO
	            	AND B.WONO=C.WONO
	            	AND C.ITEM=D.ITEM`,
				self.Usr.Conn, new(common.QueryDetail), s_bd, s_fd, s_st)
		} else {
			data_ls = *common.FetchAllRowsPtr(
				`SELECT TOP 10 ROW_NUMBER() OVER(ORDER BY A.MODIFYDATE) AS AUTOID, B.WONO, C.ITEM, 
						 ISNULL(D.DESCRIP, ''), A.SSN_NO, A.CURR_STATION, A.RESULT, CONVERT(CHAR(23), A.MODIFYDATE, 121)
	           	   FROM SFC_SSNFLOW A(NOLOCK), SFC_SSN B(NOLOCK), SFC_WO C(NOLOCK), SFC_ITEMS D(NOLOCK) 
	          	  WHERE A.MODIFYDATE BETWEEN ? AND ?
			    	AND A.CURR_STATION=?
	            	AND A.RESULT='PASS'
	            	AND A.SSN_NO=B.SSN_NO
	            	AND B.WONO=C.WONO
	            	AND C.ITEM=D.ITEM
	            	AND C.ITEM=?`,
				self.Usr.Conn, new(common.QueryDetail), s_bd, s_fd, s_st, s_im)
		}
	}

	rec_curr, _ := strconv.Atoi(s_nm)
	total_id := rec_curr / 10
	if rec_curr%10 > 0 {
		total_id++
	}

	self.TmplData["data_ls"] = data_ls
	self.TmplData["curr_page"] = "1"
	self.TmplData["total_page"] = total_id
	self.TmplData["item"] = s_im
	self.TmplData["station"] = s_st
	self.TmplData["begind"] = s_bd
	self.TmplData["endd"] = s_fd
}

func KanBanPassDetailHandler(ctx *web.Context) {
	self := new(QueryKanBanPassObj)
	self.Ctx = ctx
	self.Html = "kbpassdetail.html"
	self.Rightlist = []string{"kanban"}
	self.DoQueryUrl(self)
}

func (self *KanBanPassPageObj) ParameterCheck() bool {
	pageid := self.Ctx.Params["pageid"] // current page
	s_st := self.Ctx.Params["st"]       // station
	s_im := self.Ctx.Params["im"]       // item
	s_bd := self.Ctx.Params["bd"]       // begin date
	s_fd := self.Ctx.Params["fd"]       // end date

	s_id, err := strconv.Atoi(pageid)
	if err != nil {
		return false
	}
	idx := 10 * (s_id - 1)

	self.Sqlparas = append(self.Sqlparas, s_st)
	self.Sqlparas = append(self.Sqlparas, s_im)
	self.Sqlparas = append(self.Sqlparas, s_bd)
	self.Sqlparas = append(self.Sqlparas, s_fd)
	self.Sqlparas = append(self.Sqlparas, idx)

	return true
}

func (self *KanBanPassPageObj) SqlQueryExecute() {
	var data_ls []interface{}
	s_st := self.Sqlparas[0].(string)
	s_im := self.Sqlparas[1].(string)
	s_bd := self.Sqlparas[2].(string)
	s_fd := self.Sqlparas[3].(string)
	idx := self.Sqlparas[4].(int)
	if s_st == "Total" {
		if s_im == "All" {
			data_ls = *common.FetchAllRowsPtr(
				`SELECT TOP 10 A.AUTOID, A.WONO, A.ITEM, ISNULL(A.DESCRIP, ''), A.SSN_NO, A.CURR_STATION, 
                			A.RESULT, CONVERT(CHAR(23), A.MODIFYDATE, 121)
	          	   FROM
	             		(
		                	SELECT ROW_NUMBER() OVER(ORDER BY A.MODIFYDATE) AS AUTOID, B.WONO, C.ITEM, 
		                			ISNULL(D.DESCRIP, '') AS DESCRIP, A.SSN_NO, A.CURR_STATION, A.RESULT, 
		                			CONVERT(CHAR(23), A.MODIFYDATE, 121) AS MODIFYDATE
			           	   	  FROM SFC_SSNFLOW A(NOLOCK), SFC_SSN B(NOLOCK), SFC_WO C(NOLOCK), SFC_ITEMS D(NOLOCK) 
			          	  	 WHERE A.MODIFYDATE BETWEEN ? AND ?
			            	   AND A.RESULT='PASS'
			            	   AND A.SSN_NO=B.SSN_NO
			            	   AND B.WONO=C.WONO
			            	   AND C.ITEM=D.ITEM
		             	) A
	         	  WHERE A.AUTOID > ?`,
				self.Usr.Conn, new(common.QueryDetail), s_bd, s_fd, idx)
		} else {
			data_ls = *common.FetchAllRowsPtr(
				`SELECT TOP 10 A.AUTOID, A.WONO, A.ITEM, ISNULL(A.DESCRIP, ''), A.SSN_NO, A.CURR_STATION, 
                			A.RESULT, CONVERT(CHAR(23), A.MODIFYDATE, 121)
	          	   FROM
	             		(
		                	SELECT ROW_NUMBER() OVER(ORDER BY A.MODIFYDATE) AS AUTOID, B.WONO, C.ITEM, 
		                			ISNULL(D.DESCRIP, '') AS DESCRIP, A.SSN_NO, A.CURR_STATION, A.RESULT, 
		                			CONVERT(CHAR(23), A.MODIFYDATE, 121) AS MODIFYDATE
			           	   	  FROM SFC_SSNFLOW A(NOLOCK), SFC_SSN B(NOLOCK), SFC_WO C(NOLOCK), SFC_ITEMS D(NOLOCK) 
			          	  	 WHERE A.MODIFYDATE BETWEEN ? AND ?
			            	   AND A.RESULT='PASS'
			            	   AND A.SSN_NO=B.SSN_NO
			            	   AND B.WONO=C.WONO
			            	   AND C.ITEM=D.ITEM
			            	   AND C.ITEM=?
	             		) A
         	      WHERE A.AUTOID > ?`,
				self.Usr.Conn, new(common.QueryDetail), s_bd, s_fd, s_im, idx)
		}
	} else {
		if s_im == "All" {
			data_ls = *common.FetchAllRowsPtr(
				`SELECT TOP 10 A.AUTOID, A.WONO, A.ITEM, ISNULL(A.DESCRIP, ''), A.SSN_NO, A.CURR_STATION, 
                			A.RESULT, CONVERT(CHAR(23), A.MODIFYDATE, 121)
	          	   FROM
	             		(
		                	SELECT ROW_NUMBER() OVER(ORDER BY A.MODIFYDATE) AS AUTOID, B.WONO, C.ITEM, 
		                			ISNULL(D.DESCRIP, '') AS DESCRIP, A.SSN_NO, A.CURR_STATION, A.RESULT, 
		                			CONVERT(CHAR(23), A.MODIFYDATE, 121) AS MODIFYDATE
			           	   	  FROM SFC_SSNFLOW A(NOLOCK), SFC_SSN B(NOLOCK), SFC_WO C(NOLOCK), SFC_ITEMS D(NOLOCK) 
			          	  	 WHERE A.MODIFYDATE BETWEEN ? AND ?
					    	   AND A.CURR_STATION=?
			            	   AND A.RESULT='PASS'
			            	   AND A.SSN_NO=B.SSN_NO
			            	   AND B.WONO=C.WONO
			            	   AND C.ITEM=D.ITEM
	             		) A
         	      WHERE A.AUTOID > ?`,
				self.Usr.Conn, new(common.QueryDetail), s_bd, s_fd, s_st, idx)
		} else {
			data_ls = *common.FetchAllRowsPtr(
				`SELECT TOP 10 A.AUTOID, A.WONO, A.ITEM, ISNULL(A.DESCRIP, ''), A.SSN_NO, A.CURR_STATION, 
                			A.RESULT, CONVERT(CHAR(23), A.MODIFYDATE, 121)
	          	   FROM
	             		(
		                	SELECT ROW_NUMBER() OVER(ORDER BY A.MODIFYDATE) AS AUTOID, B.WONO, C.ITEM, 
		                			ISNULL(D.DESCRIP, '') AS DESCRIP, A.SSN_NO, A.CURR_STATION, A.RESULT, 
		                			CONVERT(CHAR(23), A.MODIFYDATE, 121) AS MODIFYDATE
			           	   	  FROM SFC_SSNFLOW A(NOLOCK), SFC_SSN B(NOLOCK), SFC_WO C(NOLOCK), SFC_ITEMS D(NOLOCK) 
			          	  	 WHERE A.MODIFYDATE BETWEEN ? AND ?
					    	   AND A.CURR_STATION=?
			            	   AND A.RESULT='PASS'
			            	   AND A.SSN_NO=B.SSN_NO
			            	   AND B.WONO=C.WONO
			            	   AND C.ITEM=D.ITEM
			            	   AND C.ITEM=?
	             		) A
         	      WHERE A.AUTOID > ?`,
				self.Usr.Conn, new(common.QueryDetail), s_bd, s_fd, s_st, s_im, idx)
		}
	}

	dict_js, _ := json.Marshal(map[string]interface{}{"details": data_ls, "lang": self.Lang})
	self.Ctx.ResponseWriter.Write(dict_js)
}

func SplitKanBanPassByPageHandler(ctx *web.Context) {
	self := new(KanBanPassPageObj)
	self.Ctx = ctx
	self.DoJsonQuery(self)
}

func (self *QueryKanBanFailObj) ParameterCheck() bool {
	s_nm := self.Ctx.Params["nm"] // query result data number
	s_st := self.Ctx.Params["st"] // station
	s_im := self.Ctx.Params["im"] // item
	s_bd := self.Ctx.Params["bd"] // begin date
	s_fd := self.Ctx.Params["fd"] // end date

	self.Sqlparas = append(self.Sqlparas, s_nm)
	self.Sqlparas = append(self.Sqlparas, s_st)
	self.Sqlparas = append(self.Sqlparas, s_im)
	self.Sqlparas = append(self.Sqlparas, s_bd)
	self.Sqlparas = append(self.Sqlparas, s_fd)

	return true
}

func (self *QueryKanBanFailObj) SqlQueryExecute() {
	s_nm := self.Sqlparas[0].(string)
	s_st := self.Sqlparas[1].(string)
	s_im := self.Sqlparas[2].(string)
	s_bd := self.Sqlparas[3].(string)
	s_fd := self.Sqlparas[4].(string)
	var data_ls []interface{}
	if s_st == "Total" {
		if s_im == "All" {
			data_ls = *common.FetchAllRowsPtr(
				`SELECT TOP 50 ROW_NUMBER() OVER(ORDER BY A.SSN_NO, A.MODIFYDATE) AS AUTOID, B.WONO, A.SSN_NO, A.CURR_STATION, 
						 A.RESULT, ISNULL(A.FAILCODE, ''), ISNULL(A.FAILDESC, ''), CONVERT(CHAR(23), A.MODIFYDATE, 121)
	           	   FROM SFC_SSNFLOW A(NOLOCK), SFC_SSN B(NOLOCK), SFC_WO C(NOLOCK), SFC_ITEMS D(NOLOCK) 
	          	  WHERE A.MODIFYDATE BETWEEN ? AND ?
	            	AND A.RESULT='FAIL'
	            	AND A.SSN_NO=B.SSN_NO
	            	AND B.WONO=C.WONO
	            	AND C.ITEM=D.ITEM`,
				self.Usr.Conn, new(common.WoFailInfo), s_bd, s_fd)
		} else {
			data_ls = *common.FetchAllRowsPtr(
				`SELECT TOP 50 ROW_NUMBER() OVER(ORDER BY A.SSN_NO, A.MODIFYDATE) AS AUTOID, B.WONO, A.SSN_NO, A.CURR_STATION, 
						 A.RESULT, ISNULL(A.FAILCODE, ''), ISNULL(A.FAILDESC, ''), CONVERT(CHAR(23), A.MODIFYDATE, 121)
	           	   FROM SFC_SSNFLOW A(NOLOCK), SFC_SSN B(NOLOCK), SFC_WO C(NOLOCK), SFC_ITEMS D(NOLOCK) 
	          	  WHERE A.MODIFYDATE BETWEEN ? AND ?
	            	AND A.RESULT='FAIL'
	            	AND A.SSN_NO=B.SSN_NO
	            	AND B.WONO=C.WONO
	            	AND C.ITEM=D.ITEM
	            	AND C.ITEM=?`,
				self.Usr.Conn, new(common.WoFailInfo), s_bd, s_fd, s_im)
		}
	} else {
		if s_im == "All" {
			data_ls = *common.FetchAllRowsPtr(
				`SELECT TOP 50 ROW_NUMBER() OVER(ORDER BY A.SSN_NO, A.MODIFYDATE) AS AUTOID, B.WONO, A.SSN_NO, A.CURR_STATION, 
						 A.RESULT, ISNULL(A.FAILCODE, ''), ISNULL(A.FAILDESC, ''), CONVERT(CHAR(23), A.MODIFYDATE, 121)
	           	   FROM SFC_SSNFLOW A(NOLOCK), SFC_SSN B(NOLOCK), SFC_WO C(NOLOCK), SFC_ITEMS D(NOLOCK) 
	          	  WHERE A.MODIFYDATE BETWEEN ? AND ?
	          	    AND A.CURR_STATION=?
	            	AND A.RESULT='FAIL'
	            	AND A.SSN_NO=B.SSN_NO
	            	AND B.WONO=C.WONO
	            	AND C.ITEM=D.ITEM`,
				self.Usr.Conn, new(common.WoFailInfo), s_bd, s_fd, s_st)
		} else {
			data_ls = *common.FetchAllRowsPtr(
				`SELECT TOP 50 ROW_NUMBER() OVER(ORDER BY A.SSN_NO, A.MODIFYDATE) AS AUTOID, B.WONO, A.SSN_NO, A.CURR_STATION, 
						 A.RESULT, ISNULL(A.FAILCODE, ''), ISNULL(A.FAILDESC, ''), CONVERT(CHAR(23), A.MODIFYDATE, 121)
	           	   FROM SFC_SSNFLOW A(NOLOCK), SFC_SSN B(NOLOCK), SFC_WO C(NOLOCK), SFC_ITEMS D(NOLOCK) 
	          	  WHERE A.MODIFYDATE BETWEEN ? AND ?
	          	    AND A.CURR_STATION=?
	            	AND A.RESULT='FAIL'
	            	AND A.SSN_NO=B.SSN_NO
	            	AND B.WONO=C.WONO
	            	AND C.ITEM=D.ITEM
	            	AND C.ITEM=?`,
				self.Usr.Conn, new(common.WoFailInfo), s_bd, s_fd, s_st, s_im)
		}
	}

	rec_curr, _ := strconv.Atoi(s_nm)
	total_id := rec_curr / 50
	if rec_curr%50 > 0 {
		total_id++
	}

	self.TmplData["data_ls"] = data_ls
	self.TmplData["curr_page"] = "1"
	self.TmplData["total_page"] = total_id
	self.TmplData["item"] = s_im
	self.TmplData["station"] = s_st
	self.TmplData["begind"] = s_bd
	self.TmplData["endd"] = s_fd
}

func KanBanFailDetailHandler(ctx *web.Context) {
	self := new(QueryKanBanFailObj)
	self.Ctx = ctx
	self.Html = "kbfaildetail.html"
	self.Rightlist = []string{"kanban"}
	self.DoQueryUrl(self)
}

func (self *KanBanFailPageObj) ParameterCheck() bool {
	pageid := self.Ctx.Params["pageid"] // current page
	s_st := self.Ctx.Params["st"]       // station
	s_im := self.Ctx.Params["im"]       // item
	s_bd := self.Ctx.Params["bd"]       // begin date
	s_fd := self.Ctx.Params["fd"]       // end date

	s_id, err := strconv.Atoi(pageid)
	if err != nil {
		return false
	}
	idx := 50 * (s_id - 1)

	self.Sqlparas = append(self.Sqlparas, s_st)
	self.Sqlparas = append(self.Sqlparas, s_im)
	self.Sqlparas = append(self.Sqlparas, s_bd)
	self.Sqlparas = append(self.Sqlparas, s_fd)
	self.Sqlparas = append(self.Sqlparas, idx)

	return true
}

func (self *KanBanFailPageObj) SqlQueryExecute() {
	var data_ls []interface{}
	s_st := self.Sqlparas[0].(string)
	s_im := self.Sqlparas[1].(string)
	s_bd := self.Sqlparas[2].(string)
	s_fd := self.Sqlparas[3].(string)
	idx := self.Sqlparas[4].(int)
	if s_st == "Total" {
		if s_im == "All" {
			data_ls = *common.FetchAllRowsPtr(
				`SELECT TOP 50 A.AUTOID, A.WONO, A.SSN_NO, A.CURR_STATION, A.RESULT, A.FAILCODE, 
						 A.FAILDESC, A.MODIFYDATE
	          	   FROM
	             		(
		                	SELECT ROW_NUMBER() OVER(ORDER BY A.SSN_NO, A.MODIFYDATE) AS AUTOID, B.WONO, 
		                			A.SSN_NO, A.CURR_STATION, A.RESULT, ISNULL(A.FAILCODE, '') AS FAILCODE, 
		                			ISNULL(A.FAILDESC, '') AS FAILDESC, CONVERT(CHAR(23), A.MODIFYDATE, 121) AS MODIFYDATE
			           	   	  FROM SFC_SSNFLOW A(NOLOCK), SFC_SSN B(NOLOCK), SFC_WO C(NOLOCK), SFC_ITEMS D(NOLOCK) 
			          	  	 WHERE A.MODIFYDATE BETWEEN ? AND ?
			            	   AND A.RESULT='FAIL'
			            	   AND A.SSN_NO=B.SSN_NO
			            	   AND B.WONO=C.WONO
			            	   AND C.ITEM=D.ITEM
	             		) A
	         	  WHERE A.AUTOID > ?`,
				self.Usr.Conn, new(common.WoFailInfo), s_bd, s_fd, idx)
		} else {
			data_ls = *common.FetchAllRowsPtr(
				`SELECT TOP 50 A.AUTOID, A.WONO, A.SSN_NO, A.CURR_STATION, A.RESULT, A.FAILCODE, 
						 A.FAILDESC, A.MODIFYDATE
	          	   FROM
	             		(
		                	SELECT ROW_NUMBER() OVER(ORDER BY A.SSN_NO, A.MODIFYDATE) AS AUTOID, B.WONO, 
		                			A.SSN_NO, A.CURR_STATION, A.RESULT, ISNULL(A.FAILCODE, '') AS FAILCODE, 
		                			ISNULL(A.FAILDESC, '') AS FAILDESC, CONVERT(CHAR(23), A.MODIFYDATE, 121) AS MODIFYDATE
			           	   	  FROM SFC_SSNFLOW A(NOLOCK), SFC_SSN B(NOLOCK), SFC_WO C(NOLOCK), SFC_ITEMS D(NOLOCK) 
			          	  	 WHERE A.MODIFYDATE BETWEEN ? AND ?
			            	   AND A.RESULT='FAIL'
			            	   AND A.SSN_NO=B.SSN_NO
			            	   AND B.WONO=C.WONO
			            	   AND C.ITEM=D.ITEM
			            	   AND C.ITEM=?
	             		) A
	         	  WHERE A.AUTOID > ?`,
				self.Usr.Conn, new(common.WoFailInfo), s_bd, s_fd, s_im, idx)
		}
	} else {
		if s_im == "All" {
			data_ls = *common.FetchAllRowsPtr(
				`SELECT TOP 50 A.AUTOID, A.WONO, A.SSN_NO, A.CURR_STATION, A.RESULT, A.FAILCODE, 
						 A.FAILDESC, A.MODIFYDATE
	          	   FROM
	             		(
		                	SELECT ROW_NUMBER() OVER(ORDER BY A.SSN_NO, A.MODIFYDATE) AS AUTOID, B.WONO, 
		                			A.SSN_NO, A.CURR_STATION, A.RESULT, ISNULL(A.FAILCODE, '') AS FAILCODE, 
		                			ISNULL(A.FAILDESC, '') AS FAILDESC, CONVERT(CHAR(23), A.MODIFYDATE, 121) AS MODIFYDATE
			           	   	  FROM SFC_SSNFLOW A(NOLOCK), SFC_SSN B(NOLOCK), SFC_WO C(NOLOCK), SFC_ITEMS D(NOLOCK) 
			          	  	 WHERE A.MODIFYDATE BETWEEN ? AND ?
			          	  	   AND A.CURR_STATION=?
			            	   AND A.RESULT='FAIL'
			            	   AND A.SSN_NO=B.SSN_NO
			            	   AND B.WONO=C.WONO
			            	   AND C.ITEM=D.ITEM
	             		) A
	         	  WHERE A.AUTOID > ?`,
				self.Usr.Conn, new(common.WoFailInfo), s_bd, s_fd, s_st, idx)
		} else {
			data_ls = *common.FetchAllRowsPtr(
				`SELECT TOP 50 A.AUTOID, A.WONO, A.SSN_NO, A.CURR_STATION, A.RESULT, A.FAILCODE, 
						 A.FAILDESC, A.MODIFYDATE
	          	   FROM
	             		(
		                	SELECT ROW_NUMBER() OVER(ORDER BY A.SSN_NO, A.MODIFYDATE) AS AUTOID, B.WONO, 
		                			A.SSN_NO, A.CURR_STATION, A.RESULT, ISNULL(A.FAILCODE, '') AS FAILCODE, 
		                			ISNULL(A.FAILDESC, '') AS FAILDESC, CONVERT(CHAR(23), A.MODIFYDATE, 121) AS MODIFYDATE
			           	   	  FROM SFC_SSNFLOW A(NOLOCK), SFC_SSN B(NOLOCK), SFC_WO C(NOLOCK), SFC_ITEMS D(NOLOCK) 
			          	  	 WHERE A.MODIFYDATE BETWEEN ? AND ?
			          	  	   AND A.CURR_STATION=?
			            	   AND A.RESULT='FAIL'
			            	   AND A.SSN_NO=B.SSN_NO
			            	   AND B.WONO=C.WONO
			            	   AND C.ITEM=D.ITEM
			            	   AND C.ITEM=?
	             		) A
	         	  WHERE A.AUTOID > ?`,
				self.Usr.Conn, new(common.WoFailInfo), s_bd, s_fd, s_st, s_im, idx)
		}
	}

	dict_js, _ := json.Marshal(map[string]interface{}{"details": data_ls, "lang": self.Lang})
	self.Ctx.ResponseWriter.Write(dict_js)
}

func SplitKanBanFailByPageHandler(ctx *web.Context) {
	self := new(KanBanFailPageObj)
	self.Ctx = ctx
	self.DoJsonQuery(self)
}
